#!/usr/bin/env python

# ---------------------------------------------------
# Volturnus Address Book (GUI)
# Version: svn

# Developers: Bryan Smith (apollo.zephyr) and Richard Hummel (hummel.richard)
# Website: http://code.google.com/p/volturnus
# Thanks: wxGlade and the wxPython documentation. Without these, making this
#         would have been much harder.
# Notes: Naming conventions in the grid and database are based on Canadian address
#        conventions. In other words, each individual has a postal code ande province.
#        Names have absolutely no bearing on the type of data that can go in the appropriate
#        column. If you want to change the column names for aesthetic purposes, see
#        __set_properties method.

# Copyright (C) 2007 Bryan Smith

# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
# ---------------------------------------------------

# Required Modules
# wx and wx.grid - used for creating the UI
# sqlite3 - used for creation of and maintenance of the database
# os.path - used for existence of the database (newdb method)

import wx
import wx.grid
import sqlite3
import os
import os.path as file_check

## VolFrame Class
# Controls all operations of the program.
class VolFrame(wx.Frame):

    ## __init__ Method
    #
    # Initializes the UI by creating each element (each menu element, toolbar and associated buttons and the grid).
    # @param self
    # @param *args
    # @param **kwds
    def __init__(self, *args, **kwds):
        # set frame style to the default
        kwds["style"] = wx.DEFAULT_FRAME_STYLE
        wx.Frame.__init__(self, *args, **kwds)
        
        # Main Menu Bar
        self.mainFrame_menubar = wx.MenuBar()
        self.menuFile = wx.Menu()
        # File -> New DB menu
        self.file_newdb = wx.MenuItem(self.menuFile, wx.NewId(), "New DB\tCtrl+N", "", wx.ITEM_NORMAL)
        self.menuFile.AppendItem(self.file_newdb)
        # File -> Import Menu
        self.file_importdb = wx.MenuItem(self.menuFile, wx.NewId(), "Import DB\tCtrl+O", "", wx.ITEM_NORMAL)
        self.menuFile.AppendItem(self.file_importdb)
        # File -> Export Menu
        self.file_exportdb = wx.MenuItem(self.menuFile, wx.NewId(), "Export DB\tCtrl+S", "", wx.ITEM_NORMAL)
        self.menuFile.AppendItem(self.file_exportdb)
        # Separator
        self.menuFile.AppendSeparator()
        # File - > About menu
        self.file_about = wx.MenuItem(self.menuFile, wx.NewId(), "About", "", wx.ITEM_NORMAL)
        self.menuFile.AppendItem(self.file_about)
        # File -> Quit Menu
        self.file_quit = wx.MenuItem(self.menuFile, wx.NewId(), "Quit\tCtrl-Q", "", wx.ITEM_NORMAL)
        self.menuFile.AppendItem(self.file_quit)
        # File menu
        self.mainFrame_menubar.Append(self.menuFile, "File")
        self.menuContacts = wx.Menu()
        # Contacts -> Add Name
        self.contacts_add_name = wx.MenuItem(self.menuContacts, wx.NewId(), "Add Name\tCtrl+A", "", wx.ITEM_NORMAL)
        self.menuContacts.AppendItem(self.contacts_add_name)
        # Contacts -> Delete Name
        self.contacts_delete_name = wx.MenuItem(self.menuContacts, wx.NewId(), "Delete Name\tCtrl+D", "", wx.ITEM_NORMAL)
        self.menuContacts.AppendItem(self.contacts_delete_name)
        # Contacts -> Edit Name
        self.contacts_edit_name = wx.MenuItem(self.menuContacts, wx.NewId(), "Edit Name\tCtrl+E", "", wx.ITEM_NORMAL)
        self.menuContacts.AppendItem(self.contacts_edit_name)
        # Separator
        self.menuContacts.AppendSeparator()
        # Contacts -> Lookup (Last Name)
        self.contacts_lookup_last_name = wx.MenuItem(self.menuContacts, wx.NewId(), "Lookup (Last Name)\tCtrl+L", "", wx.ITEM_NORMAL)
        self.menuContacts.AppendItem(self.contacts_lookup_last_name)
        # Contacts -> Lookup (Town/City)
        self.contacts_lookup_town = wx.MenuItem(self.menuContacts, wx.NewId(), "Lookup (Town/City)\tCtrl+T", "", wx.ITEM_NORMAL)
        self.menuContacts.AppendItem(self.contacts_lookup_town)
        # Separator
        self.menuContacts.AppendSeparator()
        # Contacts -> View All Names
        self.contacts_view_all_names = wx.MenuItem(self.menuContacts, wx.NewId(), "View All Names\tCtrl+V", "", wx.ITEM_NORMAL)
        self.menuContacts.AppendItem(self.contacts_view_all_names)
        # Contacts
        self.mainFrame_menubar.Append(self.menuContacts, "Contacts")
        self.menuExport = wx.Menu()
        # Export -> To HTML
        self.exportHTML = wx.MenuItem(self.menuExport, wx.NewId(), "To HTML\tCtrl+H", "", wx.ITEM_NORMAL)
        self.menuExport.AppendItem(self.exportHTML)
        # Export -> To CSV
        self.exportCSV = wx.MenuItem(self.menuExport, wx.NewId(), "To CSV\tCtrl+C", "", wx.ITEM_NORMAL)
        self.menuExport.AppendItem(self.exportCSV)
        # Export
        self.mainFrame_menubar.Append(self.menuExport, "Export")
        self.SetMenuBar(self.mainFrame_menubar)
        
        # Toolbar
        ids = [wx.NewId(), wx.NewId(), wx.NewId(), wx.NewId(), wx.NewId(), wx.NewId(), wx.NewId(), wx.NewId(), wx.NewId(), wx.NewId(), wx.NewId(), wx.NewId(), wx.NewId() ]
        self.mainFrame_tb = wx.ToolBar(self, -1, style=wx.TB_HORIZONTAL)
        self.SetToolBar(self.mainFrame_tb)
        self.mainFrame_tb.AddLabelTool(ids[0], "New DB", wx.Bitmap("icons/new.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")
        self.mainFrame_tb.AddLabelTool(ids[1], "Import DB", wx.Bitmap("icons/import.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")        
        self.mainFrame_tb.AddLabelTool(ids[2], "Export DB", wx.Bitmap("icons/export.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")
        self.mainFrame_tb.AddSeparator()                
        self.mainFrame_tb.AddLabelTool(ids[3], "Add Name", wx.Bitmap("icons/add_name.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")
        self.mainFrame_tb.AddLabelTool(ids[4], "Delete Name", wx.Bitmap("icons/delete_name.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")                
        self.mainFrame_tb.AddLabelTool(ids[5], "Edit Name", wx.Bitmap("icons/edit_name.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")
        self.mainFrame_tb.AddSeparator()
        self.mainFrame_tb.AddLabelTool(ids[6], "Lookup (Last Name)", wx.Bitmap("icons/lookup_last_name.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")
        self.mainFrame_tb.AddLabelTool(ids[7], "Lookup (Town/City)", wx.Bitmap("icons/lookup_town_city.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")
        self.mainFrame_tb.AddLabelTool(ids[8], "View All Names", wx.Bitmap("icons/view_all_names.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")                                
        self.mainFrame_tb.AddSeparator()
        self.mainFrame_tb.AddLabelTool(ids[9], "Export to HTML", wx.Bitmap("icons/export_html.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")
        self.mainFrame_tb.AddLabelTool(ids[10], "Export to CSV", wx.Bitmap("icons/export_csv.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")                
        self.mainFrame_tb.AddSeparator()
        self.mainFrame_tb.AddLabelTool(ids[11], "About", wx.Bitmap("icons/about.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")        
        self.mainFrame_tb.AddLabelTool(ids[12], "Exit", wx.Bitmap("icons/quit.png", wx.BITMAP_TYPE_ANY), wx.NullBitmap, wx.ITEM_NORMAL, "", "")   
        
        # Create the data grid
        self.dataGrid = wx.grid.Grid(self, -1, size=(1, 1))

        # Set the properties and create the layout
        self.__set_properties()
        self.__do_layout()

        # Bind the events of the menu items to the appropriate menu items
        self.Bind(wx.EVT_MENU, self.about, self.file_about)
        self.Bind(wx.EVT_MENU, self.newdb, self.file_newdb)
        self.Bind(wx.EVT_MENU, self.importdb, self.file_importdb)
        self.Bind(wx.EVT_MENU, self.exportdb, self.file_exportdb)
        self.Bind(wx.EVT_MENU, self.add_name, self.contacts_add_name)
        self.Bind(wx.EVT_MENU, self.delete_name, self.contacts_delete_name)
        self.Bind(wx.EVT_MENU, self.edit_name, self.contacts_edit_name)
        self.Bind(wx.EVT_MENU, self.lookup_last_name, self.contacts_lookup_last_name)
        self.Bind(wx.EVT_MENU, self.lookup_town_city, self.contacts_lookup_town)
        self.Bind(wx.EVT_MENU, self.view_all_names, self.contacts_view_all_names)
        self.Bind(wx.EVT_MENU, self.export_to_html, self.exportHTML)
        self.Bind(wx.EVT_MENU, self.export_to_csv, self.exportCSV)
        self.Bind(wx.EVT_MENU, self.prog_quit, self.file_quit)
        self.Bind(wx.EVT_TOOL, self.newdb, id=ids[0])
        self.Bind(wx.EVT_TOOL, self.importdb, id=ids[1])
        self.Bind(wx.EVT_TOOL, self.exportdb, id=ids[2])
        self.Bind(wx.EVT_TOOL, self.add_name, id=ids[3])
        self.Bind(wx.EVT_TOOL, self.delete_name, id=ids[4])
        self.Bind(wx.EVT_TOOL, self.edit_name, id=ids[5])
        self.Bind(wx.EVT_TOOL, self.lookup_last_name, id=ids[6])
        self.Bind(wx.EVT_TOOL, self.lookup_town_city, id=ids[7])
        self.Bind(wx.EVT_TOOL, self.view_all_names, id=ids[8])
        self.Bind(wx.EVT_TOOL, self.export_to_html, id=ids[9])
        self.Bind(wx.EVT_TOOL, self.export_to_csv, id=ids[10])
        self.Bind(wx.EVT_TOOL, self.about, id=ids[11])
        self.Bind(wx.EVT_TOOL, self.prog_quit, id=ids[12])                                                                                        
        self.Bind(wx.EVT_CLOSE, self.prog_quit, self.file_quit)

        if file_check.exists('volturnus.db'): # if the database already exist in the current directory
            try:
                self.v_connection = sqlite3.connect('volturnus.db') # Connect to the db
                self.v_cursor = self.v_connection.cursor() # initialize the database and create the cursor
            except StandardError:
                # Database cannot be opened, alert user
                err = wx.MessageDialog(self, "Database open error. Check to see that the database exists and that you have read permission.", "Error", wx.OK | wx.ICON_ERROR)
                err.ShowModal()
                err.Destroy()
            # refresh list (put all the entries into the grid)
            self.refreshList()
        else:
            pass # if the database does not exist, do nothing and wait for user to create database

    ## __set_properties Method
    #
    # Sets the properties of the main window.
    # @param self
    def __set_properties(self):
        self.SetTitle("Volturnus") # title of main window
        self.mainFrame_tb.SetToolBitmapSize((22,22))
        self.mainFrame_tb.Realize() # http://www.freiburg.linux.de/~wxxt/wxHTML/wxwin365.htm#wxtoolbarenabletool - thanks for the idea
        self.SetSize((973, 482)) # size is fairly arbitrary, works fairly well at 1024x768 and 1280x1024
        self.Centre() # http://zetcode.com/wxpython/firststeps/ - thanks!
        self.dataGrid.CreateGrid(10000, 9) # 10,000 rows, 9 columns - I assume 10,000 rows is enough ;)
        self.dataGrid.SetColLabelValue(0, "Last Name") # Last Name column (goes first as looking up last name is easier than first name)
        self.dataGrid.SetColLabelValue(1, "First Name") # First Name column
        self.dataGrid.SetColLabelValue(2, "Street") # Street column
        self.dataGrid.SetColLabelValue(3, "Town/City") # Town/City column
        self.dataGrid.SetColLabelValue(4, "Province") # Province column
        self.dataGrid.SetColLabelValue(5, "Postal Code") # Postal code column
        self.dataGrid.SetColLabelValue(6, "Country") # Country column
        self.dataGrid.SetColLabelValue(7, "Home No.") # Home Phone Number column
        self.dataGrid.SetColLabelValue(8, "Work No.") # Work Phone Number column

        # editing of individual cells is not enabled (yet - to do: allow users to change cells and reflect these changes in the db)
        self.dataGrid.EnableEditing(0)

        # Column sizes are determined on the type of data required for cell
        # 0 = Last Name - names can be quite long and thus, this needs to be accomodated for
        # 1 = First Name - same reason as last name
        # 2 = Street - street names with associated street types (road, street, way...) can get long, especially if apt. number is involved
        # 5 = Postal Code - all postal codes are of the same length but default column size does not accomodate for this size
        # 7 = Home Phone Number - default column width does not accomodate for 11 numbers
        # 8 = Work Phone Number - same as home phone number
        self.dataGrid.SetColSize(0, 100)
        self.dataGrid.SetColSize(1, 100)
        self.dataGrid.SetColSize(2, 200)
        self.dataGrid.SetColSize(5, 100)
        self.dataGrid.SetColSize(7, 120)
        self.dataGrid.SetColSize(8, 120)

    ## __do_layout Method
    #
    # Establish the layout of the contents.
    # @param self
    def __do_layout(self):
        mainSizer = wx.BoxSizer(wx.HORIZONTAL) # grid will be contained in this BoxSizer
        mainSizer.Add(self.dataGrid, 1, wx.EXPAND, 0)
        self.SetSizer(mainSizer)
        self.Layout()

    ## sortNames Method
    #
    # Sort the names in the database and commit the changes.
    # @param self
    def sortNames(self):
        sort_info = "SELECT * FROM volturnus ORDER BY lname" # Select each name and order by last name (lname)
        self.v_cursor.execute(sort_info) # execute sql query
        self.v_connection.commit() # commit the ordered list as the new order

    ## refreshList Method
    #
    # Refreshs the contents of the data grid.
    # @param self
    def refreshList(self):
        # Try to get the contents of the database
        try:
            get_info = "SELECT * from volturnus"
            self.v_cursor.execute(get_info) # execute the select query
            self.sortNames() # after execution, sort the names
        except StandardError:
            # database could not be queried and as such, an error message is conveyed to the user
            err = wx.MessageDialog(self, "Retrieval failed. Check read permissions.", "Error", wx.OK | wx.ICON_ERROR)
            err.ShowModal()
            err.Destroy()
        row_num = 0 # holds the row number - starts at 0 as first row in grid is row 0
        self.dataGrid.ClearGrid() # clear the grid in case a search had been executed
        for info in self.v_cursor.fetchall():
            # set the values of each cell (row number, column number, info from the database)
            self.dataGrid.SetCellValue(row_num, 0, info[0])
            self.dataGrid.SetCellValue(row_num, 1, info[1])
            self.dataGrid.SetCellValue(row_num, 2, info[2])
            self.dataGrid.SetCellValue(row_num, 3, info[3])
            self.dataGrid.SetCellValue(row_num, 4, info[4])
            self.dataGrid.SetCellValue(row_num, 5, info[5])
            self.dataGrid.SetCellValue(row_num, 6, info[6])
            self.dataGrid.SetCellValue(row_num, 7, info[7])
            self.dataGrid.SetCellValue(row_num, 8, info[8])
            row_num += 1 # add one to row_num so the next individual's info (if there is one) will be put in the next row

    ## getInfo Method
    #
    # Gets user input from the individual. This is used in any method that requires user input (Manage Menu entries except for View All Names).
    # @param self
    # @param item - Message for the Text Entry Dialog
    # @param title - Title for the Text Entry Dialog
    def getInfo(self, item, title):
        try:
            new_info = wx.TextEntryDialog(self, item, title) # create a Text Entry Dialog for user input
            if new_info.ShowModal() == wx.ID_OK:
                return new_info.GetValue() # if the user enters something as chooses ok, then the value entered is returned
            else:
                return "NULL"
            new_info.Destroy() # destroy the Text Entry Dialog
        except UnboundLocalError:
            pass
    ## about Method
    #
    # Displays an about dialog with information about the program
    # @param self
    # @param event
    def about(self, event):
        about_vol = wx.AboutDialogInfo() # create an about dialog
        about_vol.Name = "Volturnus"
        about_vol.Version = "svn"
        about_vol.Copyright = "(c) 2007 Bryan Smith."
        about_vol.Description = "Simplistic Address Book."
        about_vol.License = "See included LICENSE.txt file.\nLicensed under the GNU General Public License v. 2." + \
                            "\n\nIf you did not get a copy of the license, you can get it here:\n" + \
                            "http://www.gnu.org/licenses/old-licenses/gpl-2.0.html\n------------------------\n" + \
                            "Icons (in the toolbar) are from the Tango Desktop Project (http://tango.freedesktop.org/Tango_Desktop_Project)\n" + \
                            "The icons are licensed under the Creative Commons Attribution Share-Alike License:\n" + \
                            "http://creativecommons.org/licenses/by-sa/2.5/"
        about_vol.Developers = ["Bryan Smith (apollo.zephyr)", "Richard Hummel (hummel.richard)"]
        wx.AboutBox(about_vol) # show an about box using the above defined parameters

    ## prog_quit Method
    #
    # Quit the program.
    # @param self
    # @param event
    def prog_quit(self, event):
        try:
            self.v_connection.close() # close the db connection
            self.Destroy() # destroy the frame
        except Exception:
            self.Destroy() # destroy the frame

    ## newdb Method
    #
    # Create a new database.
    # @param self
    # @param event
    def newdb(self, event):
        db_question = wx.MessageDialog(self, "Create a new database?", "New DB", wx.YES_NO | wx.ICON_INFORMATION) # ask if the user wants to create a new database
        if db_question.ShowModal() == wx.ID_YES:
            if file_check.exists('volturnus.db'):
                # If db exists, alert the user as there is no need to create a new one - if the db is needed
                # remove it or rename it
                # Thanks to http://forums.devshed.com/python-programming-11/how-to-see-if-file-exists-83744.html for the existance of the file tip
                err = wx.MessageDialog(self, "Database already exists. Please move it or delete it to create a new one.", "Error", wx.OK | wx.ICON_ERROR)
                err.ShowModal()
                err.Destroy()
            else:
                # executed if no db exists
                try:
                    # Connect to the db (creating one in the process if required)
                    self.v_connection = sqlite3.connect('volturnus.db')
                    self.v_cursor = self.v_connection.cursor()
                    # Create table with rows for last name, first name, street, town, province,
                    # postal code, country and phone number
                    # All are created as type 'text' to account for things like dashes and any extra
                    # characters thought to be appropriate
                    self.v_cursor.execute('''
                    create table volturnus (
                        lname   TEXT    PRIMARY KEY,
                        fname   TEXT,
                        street TEXT,
                        town   TEXT,
                        province TEXT,
                        postalcode TEXT,
                        country TEXT,
                        homephone  TEXT,
                        workphone  TEXT
                    )
                    ''') # Create the table that stores the data in the newly created database
                    self.v_connection.commit() # commit the info to the database
                    err = wx.MessageDialog(self, "Database created sucessfully!", "DB Created", wx.OK | wx.ICON_INFORMATION) # alert the user of a successful database creation
                    err.ShowModal()
                    err.Destroy()
                except StandardError:
                    # this should only occur if there was a problem creating the database
                    err = wx.MessageDialog(self, "Database creation error. Check that you have write permissions to this directory.", "Error", wx.OK | wx.ICON_ERROR)
                    err.ShowModal()
                    err.Destroy()
        db_question.Destroy() # destroy the question box

    ## importdb Method
    #
    # Import a previously created db.
    # @param self
    # @param event
    def importdb(self, event):
        # Create the open file dialog for db selection
        open_dialog = wx.FileDialog(self, message="Open Volturnus DB File", defaultDir=os.getcwd(), defaultFile="", wildcard="Volturnus DB File (*.vdf)|*.vdf", style=wx.OPEN | wx.CHANGE_DIR)
        if open_dialog.ShowModal() == wx.ID_OK:
            # these are the lists that will contain the contact info in the selected file
            first_name = []
            last_name = []
            street = []
            town = []
            province = []
            postal = []
            country = []
            home_no = []
            work_no = []

            try:
                open_file = open(str(open_dialog.GetPath()), "r") # open the file
                item_no = 0 # which item are we on? 0 = first name, 1 = last name...
                person_count = 0 # how many people do we have?
                for line in open_file.readlines():
                    if item_no == 0:
                        first_name.append(line.strip("\n"))
                        item_no += 1
                    elif item_no == 1:
                        last_name.append(line.strip("\n"))
                        item_no += 1
                    elif item_no == 2:
                        street.append(line.strip("\n"))
                        item_no += 1
                    elif item_no == 3:
                        town.append(line.strip("\n"))
                        item_no += 1
                    elif item_no == 4:
                        province.append(line.strip("\n"))
                        item_no += 1
                    elif item_no == 5:
                        postal.append(line.strip("\n"))
                        item_no += 1
                    elif item_no == 6:
                        country.append(line.strip("\n"))
                        item_no += 1
                    elif item_no == 7:
                        home_no.append(line.strip("\n"))
                        item_no += 1
                    elif item_no == 8:
                        work_no.append(line.strip("\n"))
                        item_no = 0
                    person_count += 1
                for num in range(person_count/9): # person count is actually 9x the actual people because one is added any piece of info is parsed
                    query = "INSERT INTO volturnus VALUES('" + last_name[num] + "','" + first_name[num] + "','" + street[num] + \
                               "','" + town[num] + "','" + province[num] + "','" + postal[num] + \
                               "','" + country[num] + "','" + home_no[num] + "','" + work_no[num] + "')"
                    self.v_cursor.execute(query) # execute the sql query
                    self.v_connection.commit() # commit the contact info to the database
                open_dialog.Destroy() # destory the file dialog
                open_file.close() # close the file
                self.refreshList() # refresh the grid to reflect the changes
            except IOError:
                err = wx.MessageDialog(self, "Error reading Volturnus Database File. Check to make sure there is a database present.", "Error", wx.OK | wx.ICON_ERROR)
                err.ShowModal()
                err.Destroy()
            
    ## exportdb Method
    #
    # Export the db.
    # @param self
    # @param event
    def exportdb(self, event):
        vdf = "" # this will hold the contents of the output vdf
        try: # try selecting the data from the db
            person_info = "SELECT * from volturnus"
            self.v_cursor.execute(person_info)
        except StandardError:
            # if there was an error selecting data from the database, inform the user
            err = wx.MessageDialog(self, "Retrieval failed. Check read permissions.", "Error", wx.OK | wx.ICON_ERROR)
            err.ShowModal()
            err.Destroy()
        # for each name, add them to the HTML formatted table
        for info in self.v_cursor.fetchall():
            vdf += info[0] + "\n" + info[1] + "\n" + info[2] +  \
                    "\n" + info[3] +"\n" + info[4] + "\n" + info[5] + \
                    "\n" + info[6] + "\n" + info[7] + "\n" + info[8] +"\n" # add the data to a row on the table
                # try to open a file (creating it if it doesn't exist)
        try:
            open_dialog = wx.FileDialog(self, message="Save Volturnus DB File", defaultDir=os.getcwd(), defaultFile="", wildcard="Volturnus DB File (*.vdf)|*.vdf", style=wx.SAVE | wx.CHANGE_DIR)
            if open_dialog.ShowModal() == wx.ID_OK:
                v_output = open(str(open_dialog.GetPath()), 'w') # open the file
                v_output.write(vdf) # write the data to a vdf
                v_output.close() # close the file

                # inform the user of a successful export
                success = wx.MessageDialog(self, "Database successfully exported!", "Success", wx.OK | wx.ICON_INFORMATION)
                success.ShowModal()
                success.Destroy()
        except IOError:
            # inform the user that there was an error exporting the contacts to an HTML file
            err = wx.MessageDialog(self, "Can not write to file. Check permissions.", "Error", wx.OK | wx.ICON_ERROR)
            err.ShowModal()
            err.Destroy()

    ## add_name Method
    #
    # Add a new name to the database.
    # @param self
    # @param event
    def add_name(self, event):
        # Get the names for each piece of user info using the getInfo method
        first_name = self.getInfo("First Name", "New Name")
        last_name = self.getInfo("Last Name", "New Name")
        if first_name == "NULL" and last_name == "NULL":
            pass
        else:
            street = self.getInfo("Street", "New Name")
            town = self.getInfo("Town/City", "New Name")
            province = self.getInfo("Province", "New Name")
            postal = self.getInfo("Postal Code", "New Name")
            country = self.getInfo("Country", "New Name")
            home_phone_number = self.getInfo("Home Phone Number", "New Name")
            work_phone_number = self.getInfo("Work Phone Number", "New Name")

            # try to insert the data into the database and commit it
            try:
                response = "INSERT INTO volturnus VALUES('" + last_name + "','" + first_name + "','" + street + \
                               "','" + town + "','" + province + "','" + postal + \
                               "','" + country + "','" + home_phone_number + "','" + work_phone_number + "')"
                    # insert the info into the database using an sql query
                self.v_cursor.execute(response) # execute the sql query
                self.v_connection.commit() # commit the contact info to the database
            except StandardError:
                # this should only occur if ther is an error writing to the database
                err = wx.MessageDialog(self, "Error inserting values. Check permissions on volturnus.db", "Error", wx.OK | wx.ICON_ERROR)
                err.ShowModal()
                err.Destroy()
            self.refreshList() #refresh the list to reflect the new user name

    ## delete_name method
    #
    # Delete an individual from the database.
    # @param self
    # @param event
    def delete_name(self, event):
        # get the first and last name using the getInfo method
        del_fname = self.getInfo("First Name", "Delete Name")
        del_lname = self.getInfo("Last Name", "Delete Name")

        # try to execute the sql query
        try:
            del_info = "SELECT * from volturnus"
            self.v_cursor.execute(del_info)
        except IOError:
            # inform the user of the inability to execute the query
            err = wx.MessageDialog(self, "Unable to execute query on volturnus.db", "Error", wx.OK | wx.ICON_ERROR)
            err.ShowModal()
            err.Destroy()
        try:
            full_name = del_fname + del_lname # add the two names together
        except TypeError:
            print "Name deletion cancelled"
        for info in self.v_cursor.fetchall():
            del_name = info[1] + info[0] # add the two names together (for comparison to full_name)
            if full_name.lower() == del_name.lower(): # Make both lower case for better comparison (ignoring case)
                # Print the name to show who should be deleted (in case it was a mistake)
                err = wx.MessageDialog(self, "Are you sure you want to delete:\n\nLast Name: " + info[0] + "\nFirst Name: " \
                                       + info[1] +"\nStreet: " + info[2] + "\nTown/City: " + info[3] + "\nProvince: " \
                                       + info[4] + "\nPostal Code: " + info[5] + "\nCountry: " + info[6] \
                                       + "\nHome Phone Number: " + info[7] + "\nWork Phone Number: " + \
                                       info[8], "Delete name", wx.YES_NO | wx.ICON_INFORMATION)
                if err.ShowModal() == wx.ID_YES:
                    # if the individual is selected for removal, then try to delete them from the database
                    try:
                        self.v_cursor.execute("DELETE FROM volturnus WHERE lname = '" + info[0] + "'" + "AND fname ='" + info[1] + "'")
                        self.v_connection.commit() # delete the contact and commit the changes to the sqlite database

                        # inform the user of the deletion
                        del_msg = wx.MessageDialog(self, "Name deleted from database.", "Delete Name", wx.OK | wx.ICON_INFORMATION)
                        del_msg.ShowModal()
                        del_msg.Destroy()

                        self.refreshList() # refresh the list to reflect the changes in the database
                    except IOError:
                        # inform the user if there was an error deleting the individual from the database
                        del_msg = wx.MessageDialog(self, "Deletion failed. Check write permissions.", "Delete Name", wx.OK | wx.ICON_ERROR)
                        del_msg.ShowModal()
                        del_msg.Destroy()   
                else:
                    pass # if the user selects not to delete the user, move on
                break # break the loop through the database as the individual has been found
        else:
            # at this point, no individuals have been found by that required name; inform the user as such
            del_msg = wx.MessageDialog(self, "Name not found.", "Delete Name", wx.OK | wx.ICON_INFORMATION)
            del_msg.ShowModal()
            del_msg.Destroy()

    ## edit_name Method
    #
    # Edit individual names in the database.
    # @param self
    # @param event
    def edit_name(self, event):
        # Get the names of the users.
        fname = self.getInfo("First Name", "Edit Name")
        lname = self.getInfo("Last Name", "Edit Name")

        # try to query the database for all the names
        try:
            edit_info = "SELECT * from volturnus"
            self.v_cursor.execute(edit_info)
        # the database could not be queried and the user is to be alerted as such
        except StandardError:
            err = wx.MessageDialog(self, "Unable to query 'volturnus.db'.", "Delete Name", wx.OK | wx.ICON_ERROR)
            err.ShowModal()
            err.Destroy()
        try:
            full_name = fname + lname # combine the first and last name inputed by the user
        except TypeError:
            pass
        for info in self.v_cursor.fetchall():
            edit_name = info[1] + info [0] # combine the current name of the row being analyzed in the database for comparison to the user inputed name
            if full_name.lower() == edit_name.lower():
                # try to edit the name
                try:
                    # ask the user which piece of info they would like to edit using a single choice dialog
                    edit_box = wx.SingleChoiceDialog(self, "Select the characteristic to edit:", "Edit Name", ["First Name", "Last Name", "Street", "Town/City", "Province", \
                                                                                                               "Postal Code", "Country", "Home Phone Number", \
                                                                                                               "Work Phone Number"], wx.CHOICEDLG_STYLE)

                    # if the user chooses ok on one of the selections, execute a query to change the required piece of info                                                                                                               
                    if edit_box.ShowModal() == wx.ID_OK:
                        if edit_box.GetStringSelection() == "First Name":
                            new_name = self.getInfo("New First Name", "Edit Name")
                            self.v_cursor.execute("UPDATE volturnus SET fname = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        elif edit_box.GetStringSelection() == "Last Name":
                            new_name = self.getInfo("New Last Name", "Edit Name")
                            self.v_cursor.execute("UPDATE volturnus SET lname = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        elif edit_box.GetStringSelection() == "Street":
                            new_name = self.getInfo("New Street", "Edit Name")
                            self.v_cursor.execute("UPDATE volturnus SET street = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        elif edit_box.GetStringSelection() == "Town/City":
                            new_name = self.getInfo("New Town/City", "Edit Name")
                            self.v_cursor.execute("UPDATE volturnus SET town = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        elif edit_box.GetStringSelection() == "Province":
                            new_name = self.getInfo("New Province", "Edit Name")
                            self.v_cursor.execute("UPDATE volturnus SET province = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        elif edit_box.GetStringSelection() == "Postal Code":
                            new_name = self.getInfo("New Postal Code", "Edit Name")
                            self.v_cursor.execute("UPDATE volturnus SET postalcode = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        elif edit_box.GetStringSelection() == "Country":
                            new_name = self.getInfo("New Country", "Edit Name")
                            self.v_cursor.execute("UPDATE volturnus SET country = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        elif edit_box.GetStringSelection() == "Home Phone Number":
                            new_name = self.getInfo("New Home Phone Number", "Edit Name")
                            self.v_cursor.execute("UPDATE volturnus SET homephone = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        elif edit_box.GetStringSelection() == "Work Phone Number":
                            new_name = self.getInfo("New Work Phone Number", "Edit Name")
                            self.v_cursor.execute("UPDATE volturnus SET workphone = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        self.v_connection.commit() # commit the change
                        edit_box.Destroy() # destroy the single choice dialog

                        # convey a success message to the user                                                                                                               
                        success = wx.MessageDialog(self, "Information successfully changed.", "Success", wx.OK | wx.ICON_INFORMATION)
                        success.ShowModal()
                        success.Destroy()
                    elif edit_box.ShowModal() == wx.ID_CANCEL:
                        failure = wx.MessageDialog(self, "Nothing changed.", "Failure", wx.OK | wx.ICON_INFORMATION)
                        failure.ShowModal()
                        failure.Destroy()                                                                             
                    self.refreshList() # refresh the lsit to reflect the new changes
                except IOError:
                    # there was an error editing the name in the database, inform the user                                                                                                               
                    err = wx.MessageDialog(self, "Can not write to database. Check permissions.", "Error", wx.OK | wx.ICON_ERROR)
                    err.ShowModal()
                    err.Destroy()
            else:
                no_name = wx.MessageDialog(self, "Name not found.", "Unknown Name", wx.OK | wx.ICON_INFORMATION)
                no_name.ShowModal()
                no_name.Destroy()
                break

    ## lookup_last_name Method
    #
    # Lookup an individual by last name.
    # @param self
    # @param event
    def lookup_last_name(self, event):
        lname = self.getInfo("Last Name", "Lookup (Last Name)")  # get the last name
        try:
            lookup_info = "SELECT * from volturnus"
            self.v_cursor.execute(lookup_info)
        except StandardError:
            # if the selection query was unsuccessful, inform the user
            err = wx.MessageDialog(self, "Retrieval failed. Check read permissions.", "Error", wx.OK | wx.ICON_ERROR)
            err.ShowModal()
            err.Destroy()

        try:
            row_num = 0 # hold the current row number
            self.dataGrid.ClearGrid() # clear the grid
            for info in self.v_cursor.fetchall():
                lookup = info[0].lower() # the name from the database
                if lookup.find(lname.lower()) > -1: # if the search string is found in the current name, add the results to the grid
                    self.dataGrid.SetCellValue(row_num, 0, info[0])
                    self.dataGrid.SetCellValue(row_num, 1, info[1])
                    self.dataGrid.SetCellValue(row_num, 2, info[2])
                    self.dataGrid.SetCellValue(row_num, 3, info[3])
                    self.dataGrid.SetCellValue(row_num, 4, info[4])
                    self.dataGrid.SetCellValue(row_num, 5, info[5])
                    self.dataGrid.SetCellValue(row_num, 6, info[6])
                    self.dataGrid.SetCellValue(row_num, 7, info[7])
                    self.dataGrid.SetCellValue(row_num, 8, info[8])
                    row_num += 1 # add one to row_num so the next name (if there is one) is added to the next row
        except AttributeError:
            pass
            self.refreshList()

    ## lookup_city_town Method
    #
    # Lookup an individual by town/city.
    # @param self
    # @param event       
    def lookup_town_city(self, event):
        town_city = self.getInfo("Town/City Name", "Lookup (Town/City)")  # get the last name
        # try the execute a select all query on the database
        try:
            lookup_info = "SELECT * from volturnus"
            self.v_cursor.execute(lookup_info)
        except StandardError:
            # if the selection query was unsuccessful, inform the user
            err = wx.MessageDialog(self, "Retrieval failed. Check read permissions.", "Error", wx.OK | wx.ICON_ERROR)
            err.ShowModal()
            err.Destroy()

        try:
            row_num = 0 # hold the current row number
            self.dataGrid.ClearGrid() # clear the grid
            for info in self.v_cursor.fetchall():
                lookup = info[3].lower() # the name from the database
                if lookup.find(town_city.lower()) > -1: # if the search string is found in the current name, add the results to the grid
                    self.dataGrid.SetCellValue(row_num, 0, info[0])
                    self.dataGrid.SetCellValue(row_num, 1, info[1])
                    self.dataGrid.SetCellValue(row_num, 2, info[2])
                    self.dataGrid.SetCellValue(row_num, 3, info[3])
                    self.dataGrid.SetCellValue(row_num, 4, info[4])
                    self.dataGrid.SetCellValue(row_num, 5, info[5])
                    self.dataGrid.SetCellValue(row_num, 6, info[6])
                    self.dataGrid.SetCellValue(row_num, 7, info[7])
                    self.dataGrid.SetCellValue(row_num, 8, info[8])
                    row_num += 1 # add one to row_num so the next name (if there is one) is added to the next row
        except AttributeError:
            pass
            self.refreshList()

    ## view_all_names Method
    #
    # View all the names in the database.
    # @param self
    # @param event
    def view_all_names(self, event):
        self.refreshList() # refresh the list

    ## export_to_html Method
    #
    # Export the info of each contact to an HTML document (presented in a table).
    # @param self
    # @param event       
    def export_to_html(self, event):
         # page variable will hold html contents - as it is here, it holds the beginning of the document
        page = """
<html>
<title>Volturnus Contact List</title>
<center>
<h2>Contacts</h2>
<hr>
<table border=1 cellspacing=0>
<tr bgcolor=#008000>
<td><font color=#ffffff><center>Last Name</center></font></td>
<td><font color=#ffffff><center>First Name</center></font></td>
<td><font color=#ffffff><center>Street</center></font></td>
<td><font color=#ffffff><center>City/Town</center></font></td>
<td><font color=#ffffff><center>Province/State</center></font></td>
<td><font color=#ffffff><center>Postal/Zip Code</center></font></td>
<td><font color=#ffffff><center>Country</center></font></td>
<td><font color=#ffffff><center>Home Phone Number</center></font></td>
<td><font color=#ffffff><center>Work Phone Number</center></font></td>
</tr>
""" # this will hold the contents of the page
        try:
            page_info = "SELECT * from volturnus"
            self.v_cursor.execute(page_info)
            self.sortNames() # Sort the names before outputting them to a table
        except StandardError:
            # if there was an error selecting data from the database, inform the user
            err = wx.MessageDialog(self, "Retrieval failed. Check read permissions.", "Error", wx.OK | wx.ICON_ERROR)
            err.ShowModal()
            err.Destroy()
        # for each name, add them to the HTML formatted table
        lineno = 1
        for info in self.v_cursor.fetchall():
            if lineno % 2 == 0:
                page += "<tr bgcolor=#3deb3d>\n<td>" + info[0] + "</td>\n<td>" + info[1] + "</td>\n<td>" + info[2] +  \
                        "</td>\n<td>" + info[3] +"</td>\n<td>" + info[4] + "</td>\n<td>" + info[5] + \
                        "</td>\n<td>" + info[6] + "</td>\n<td>" + info[7] + "</td>\n<td>" + info[8] +"</td>\n</tr>\n" # add the data to a row on the table
            else:
                page += "<tr>\n<td>" + info[0] + "</td>\n<td>" + info[1] + "</td>\n<td>" + info[2] +  \
                        "</td>\n<td>" + info[3] +"</td>\n<td>" + info[4] + "</td>\n<td>" + info[5] + \
                        "</td>\n<td>" + info[6] + "</td>\n<td>" + info[7] + "</td>\n<td>" + info[8] +"</td>\n</tr>\n" # add the data to a row on the table
            lineno += 1
        page+= """</table>
<hr>
<font size=2>Table constructed using <a href="http://code.google.com/p/volturnus">Volturnus Address Book</a>.
</font>
</center>
</html>
""" # finish off the page

        # try to open a file (creating it if it doesn't exist)
        try:
            v_output = open('Volturnus Contacts.html', 'w')
            v_output.write(page) # write the string (page) with the html to contact_output.html for viewing outside of volturnus
            v_output.close() # close the file

            # inform the user of a successful export
            info = wx.MessageDialog(self, "Contacts written to 'Volturnus Contacts.html'", "Success", wx.OK | wx.ICON_INFORMATION)
            info.ShowModal()
            info.Destroy()
        except IOError:
            # inform the user that there was an error exporting the contacts to an HTML file
            err = wx.MessageDialog(self, "Can not write to file. Check permissions.", "Error", wx.OK | wx.ICON_ERROR)
            err.ShowModal()
            err.Destroy()

    ## export_to_csv Method
    #
    # Export the info of each contact to a comma separated values file.
    # @param self
    # @param event
    def export_to_csv(self, event):
        csv_contents = '' # holds the contents of the csv file

        # try to select each individual from the database
        try:
            page_info = "SELECT * from volturnus"
            self.v_cursor.execute(page_info)
            self.sortNames() # Sort the names before outputting them to csv
        except StandardError:
            # inform the user if there was an error getting the info from the database
            err = wx.MessageDialog(self, "Retrieval failed. Check read permissions.", "Error", wx.OK | wx.ICON_ERROR)
            err.ShowModal()
            err.Destroy()
        for info in self.v_cursor.fetchall():
            # for each contact, add them to the csv file
            csv_contents += "\n" + info[1] + "," + info[0] + "," + info[1] + " " + info[0] + ",,,," + info[8] \
                             + "," + info[7] + ",,,," + info[2] + "," + info[3] + "," + info[4] + "," + info[5] \
                             + "," + info[6] + ",,,,,,,,,,,,,,,,,,,"

        # try to open a file - creating it if it doesn't exist                                                                                    
        try:                                                                                                            
            v_output = open('Volturnus Contacts.csv', 'w')
            v_output.write(csv_contents) # write the string (page) with the csv to Volturnus Contacts.csv for use in alternate address books
            v_output.close() # close the file

            # inform the user of a successful export
            info = wx.MessageDialog(self, "Contacts written to 'Volturnus Contacts.csv'", "Success", wx.OK | wx.ICON_INFORMATION)
            info.ShowModal()
            info.Destroy()
        except IOError:
            # inform the user that there was an error exporting the contacts to an CSV file
            err = wx.MessageDialog(self, "Can not write to file. Check permissions.", "Error", wx.OK | wx.ICON_ERROR)
            err.ShowModal()
            err.Destroy()

class VolGUI(wx.App):
    ## OnInit Method
    #
    # Initialize the GUI.
    # @param self
    def OnInit(self):
        wx.InitAllImageHandlers()
        mainFrame = VolFrame(None, -1, "")
        self.SetTopWindow(mainFrame)
        mainFrame.Show()
        return 1

if __name__ == "__main__":
    Volturnus = VolGUI(0)
    Volturnus.MainLoop() # start the main loop
